package servlet.redar;

import com.alibaba.fastjson.JSONObject;
import po.Redar;
import po.Redar;
import utils.JdbcUtil;
import utils.ResultData;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 */
@WebServlet(urlPatterns = "/redarList")
public class RedarList extends HttpServlet {
    JdbcUtil jdbc = new JdbcUtil();
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String id = req.getParameter("user_id");
        String u_nmae =req.getParameter("use");
        String phone = req.getParameter("phone");

        Connection connection = jdbc.getConnection();

        String sql = "select * from redar where 1=1";
//获取当前页和条数
        Integer page = Integer.valueOf(req.getParameter("page"));
        Integer limit = Integer.valueOf(req.getParameter("limit"));
        Integer curent = (page-1)*limit;

        PreparedStatement preparedStatement = null;

        try {

            //记录当前占位符顺序
            int i =0;
            //动态SQL拼接
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append(sql);

            if (id!=null && id.trim().length()!=0 ){
                stringBuffer.append(" and user_id like ?");
            }
            preparedStatement = connection.prepareStatement(stringBuffer.toString());

            if (u_nmae!=null && u_nmae.trim().length()!=0){
                stringBuffer.append(" and use like ?");
                preparedStatement = connection.prepareStatement(stringBuffer.toString());

            }if (phone!=null && phone.trim().length()!=0){
                stringBuffer.append(" and phone like ?");
            }
            preparedStatement = connection.prepareStatement(stringBuffer.toString());

            if (id!=null && id.trim().length()!=0 ){
                ++i;
                preparedStatement.setString(i,"%"+id+"%");
            }
            if (u_nmae!=null && u_nmae.trim().length()!=0){
                ++i;
                preparedStatement.setString(i,"%"+u_nmae+"%");
            }
            if (phone!=null && phone.trim().length()!=0){
                ++i;
                preparedStatement.setString(i,"%"+phone+"%");
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            //光标最后一位
            resultSet.last();
            Integer count = resultSet.getRow();
            //分页sql
            stringBuffer.append(" limit ?,?");
            preparedStatement  = connection.prepareStatement(stringBuffer.toString());
            //设置分页参数占位符
            i =0;
            if (id!=null && id.trim().length()!=0 ){
                ++i;
                preparedStatement.setString(i,"%"+id+"%");
            }
            if (u_nmae!=null && u_nmae.trim().length()!=0){
                ++i;
                preparedStatement.setString(i,"%"+u_nmae+"%");
            }
            if (phone!=null && phone.trim().length()!=0){
                ++i;
                preparedStatement.setString(i,"%"+phone+"%");
            }
            ++i;
            preparedStatement.setInt(i,curent);
            ++i;
            preparedStatement.setInt(i,limit);
            resultSet = preparedStatement.executeQuery();
            List<Redar> redar = new ArrayList<>();
            while (resultSet.next()){
                Redar redar_user = new Redar();

                redar_user.setId(resultSet.getInt("id"));
                redar_user.setUser_id(resultSet.getString("user_id"));
                redar_user.setUse(resultSet.getString("use"));
                redar_user.setUse_name(resultSet.getString("use_name"));
                redar_user.setSex(resultSet.getString("sex"));
                redar_user.setPhone(resultSet.getString("phone"));
                String card_time = dateFormat(resultSet.getTimestamp("card_time"));
                redar_user.setCard_time(card_time);
//                warehouse.setUpdate_time(resultSet.getDate("update_time"));
                redar_user.setEmail(resultSet.getString("email"));

                redar.add(redar_user);

            }
            ResultData page1 = ResultData.page(redar, count.longValue());
            Object o = JSONObject.toJSON(page1);
            resp.getWriter().println(o.toString());

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public String dateFormat(Date date){
        SimpleDateFormat simpleFormatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        String format = simpleFormatter.format(date);
        return format;
    }


}
